package DBLayer;

import java.util.ArrayList;

import ModelLayer.Private;

import java.sql.*;

public class DbPrivateCustomer implements IFDBPrivateCustomer {
	private Connection con;
	//Public methods
	
	public DbPrivateCustomer()
	{
		con = DbConnection.getInstance().getDBcon();
	}
	
	@Override
	public ArrayList<Private> getAllPrivateCustomers() {
		return miscWhere("");
	}

	@Override
	public Private getCustomerByCPR(String CPR) {
		String wClause = "cpr = '" + CPR + "'";
		System.out.println("Search Customer " + wClause);
		return singleWhere(wClause);
	}

	@Override
	public Private getCustomerByName(String name) {
		String wClause = "name = '" + name + "'";
		System.out.println("Search Customer " + wClause);
		return singleWhere(wClause);
	}
	
	@Override
	public Private getCustomerById(int id) {
		String wClause = "id = '" + id + "'";
		System.out.println("Search Customer " + wClause);
		return singleWhere(wClause);
	}

	@Override
	public int insertCustomer(Private customer) throws Exception  {
		int nextID = GetMax.getMaxId("SELECT max(id) from Contact");
		nextID++;
		int rc = -1;
		String insertQuery="INSERT INTO Contact(id, address, city, zipCode, phoneNo, eMail, type, status";
		String valueQuery = " VALUES('"+
				nextID  + "','"  +
				customer.getAddress() + "','"  +
				customer.getCity() + "','"  +
				customer.getZipCode() + "','"  +
				customer.getPhoneNO() + "','"  +
				customer.getEMail() + "','"  +
				customer.getStatus() + "'";

		String queryContact = insertQuery + ") " + valueQuery + ")";
		
		insertQuery="INSERT INTO Private(id, cpr, name, surname)";
		valueQuery=" VALUES('"+nextID + "','"  +
				customer.getCPR() + "','"  +
				customer.getName() + "','" +
				customer.getSurname()  + "'";
		
		String queryPrivate = insertQuery + ") " + valueQuery + ")";
		
		System.out.println("insert Contact: " + queryContact);
		System.out.println("insert Private: " + queryPrivate);
		try{ // insert new customer
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(queryContact);
			rc = stmt.executeUpdate(queryPrivate);
			stmt.close();
		}//end try
		catch(SQLException ex){
			System.out.println("Customer not created");
			throw new Exception("Customer is not inserted correctly");
		}
		return(rc);
	}

	@Override
	public int updateCustomer(Private customer) {
		int rc=-1;

		String queryContact="UPDATE Contact SET "+
				"address = '" + customer.getAddress() + "', "+
				"city = '" + customer.getCity() + "', " +
				"zipCode = '" + customer.getZipCode() + "', " +
				"phoneNo = '" + customer.getPhoneNO() + "', " +
				"eMail = '" + customer.getEMail() + "', " +
				"status = '" + customer.getStatus() + "'" +
				" WHERE id = '"+ customer.getId() + "'";
		
		String queryPrivate="UDATE Private SET "+
				"cpr = '"+ customer.getCPR()+ "', " +
				"name = '"+ customer.getName() + "', " +
				"surname = '"+ customer.getSurname() + "'" +
				" WHERE id = '"+ customer.getId() + "'";
		
		System.out.println("Update Contact: " + queryContact);
		System.out.println("Update Private: " + queryPrivate);
		try{ // update Contact + Private
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(queryContact);
			rc = stmt.executeUpdate(queryPrivate);

			stmt.close();
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in Private db: "+ex);
		}
		return(rc);
	}

	@Override
	public int deleteCustomer(Private customer) {
		int rc=-1;

		String query="DELETE FROM Private WHERE id = '" +
				customer.getId() + "'";
		System.out.println(query);
		try{ // delete from private
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();  
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in Private db: "+ex);
		}
		return(rc);
	}

	//private methods

	//michWere is used whenever we want to select more than one private customer
	private ArrayList<Private> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Private> list = new ArrayList<Private>();	
		String query =  buildQuery(wClause);

		try{ // read the PrivateCustomer from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while( results.next() )
			{
				Private custObj = new Private();
				custObj = buildCustomer(results);				
				list.add(custObj);		
			}//end while
			stmt.close();     

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}

		return list;
	}

	//Singlewhere to select only one Private Customer 	
	private Private singleWhere(String wClause)
	{
		ResultSet results;
		Private custObj = new Private();
		
	
			String query =  buildQuery(wClause);
			System.out.println(query);
			try{ // read the Private Customer from the database
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);

				if( results.next() ){
					custObj = buildCustomer(results);
					stmt.close();
				}		
				else{ //no customer was found
					custObj = null;
				}
			}
			catch(Exception e){
				System.out.println("Query exception: "+e);
			}

		return custObj;
	}

	//method to build the query for Customer
	private String buildQuery(String wClause)
	{
		String query="SELECT c.id, address, city, zipCode, phoneNo, eMail, status, "
				+ "cpr, name, surname FROM Contact c, Private p "
				+ "WHERE p.id = c.id";
		
		if (wClause.length()>0)
			query=query+" AND " + wClause;

		return query;
	}

	//method to build a Customer object
	private Private buildCustomer(ResultSet results)
	{   
		Private customerObj = new Private();
		try{ // the columns from the table customer  are used
			customerObj.setId(results.getInt("id"));
			customerObj.setAddress(results.getString("address"));
			customerObj.setCity(results.getString("city"));
			customerObj.setZipCode(results.getString("zipCode"));
			customerObj.setPhoneNO(results.getString("phoneNo"));
			customerObj.setEMail(results.getString("eMail"));
			customerObj.setStatus(results.getString("status"));
			customerObj.setCPR(results.getString("cpr"));
			customerObj.setName(results.getString("name"));
			customerObj.setSurname(results.getString("surname"));
		}
		catch(Exception e)
		{
			System.out.println("error in building the customer object");
		}
		return customerObj;
	}
}